package assignment2;

import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Vector;
import org.hibernate.*;
import com.tsystems.casou.hibernate.*;
import DBHibernate.*;

public class Assignment {

	/***
	 * This function validates that a sting is not null and empty.
	 * @param s - String, the string to validate.
	 * @return boolean value - true if is not null and not empty; false otherwise.
	 */
	private static boolean validateString(String s){
		return (s != null && !s.isEmpty());
	}

	/***
	 * The function returns true if the received username exist in the table 
	 * USERS otherwise false.
	 * @param username - String, the name of the user to check in the table USERS.  
	 * @return boolean value - true if the received username exist in the table
	 * 		   USERS; otherwise false.
	 */
	public static boolean isExistUsername(String username){
		Session session = null;
		boolean ans = false;
		if(validateString(username)){
			try{
				session = HibernateUtil.currentSession();
				String query = "from Users where username = '" + username +"'";
				List<Users> usersList = session.createQuery(query).list();

				//ans is getting true if something returned from USERS table
				ans = (usersList.size() > 0);

			}catch(HibernateException e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}
		return ans;
	}

	/***
	 * This function validates that given string represent a valid date.
	 * @param day - String, day of month. cannot be more than 31.
	 * @param month - String, self-explanatory.
	 * @param year - String, self-explanatory.
	 * @return boolean value - true if the strings represent a valid date; false otherwise.
	 */
	private static boolean validateDate(String day, String month, String year){
		boolean ans = true;

		//validate the strings
		ans = (validateString(day) && validateString(month) && validateString(year));
		if(ans){
			try{
				int dayInt = Integer.parseInt(day);
				int monthInt = Integer.parseInt(month);
				int yearInt = Integer.parseInt(year);
				Calendar cal = new GregorianCalendar();

				//don't let day be more than 31
				cal.setLenient(false); 
				//Month value is 0-based. e.g., 0 for January.
				cal.set(yearInt, monthInt-1, dayInt);
				//this will throw an exception if the date is not valid:
				cal.getTime();
			}catch(Exception e){
				ans = false;
			}
		}

		return ans;
	}

	/***
	 * The function checks if the username exist in the USERS table, in case of 
	 * positive answer returns null else insert the user to the table USERS including 
	 * registration_date field and returns the user id.
	 * @param username - String, the requested username.
	 * @param password - String, the requested password.
	 * @param first_name - String, the first name of the user.
	 * @param last_name - String, the last name of the user.
	 * @param day_of_birth - String, self-explanatory.
	 * @param month_of_birth - String, self-explanatory.
	 * @param year_of_birth - String, self-explanatory.
	 * @return null if the user already exist in the table USERS; otherwise insert 
	 * 		   the user to the table USERS and returns the user id.
	 */
	public static Integer insertUser(String username, String password, String first_name, 
			String last_name, String day_of_birth, String month_of_birth, 
			String year_of_birth) {

		//checks if the username is already taken
		//username is been validated in isExistUsername
		boolean existUsername = isExistUsername(username);
		Integer userid = null;
		if(validateString(password) && validateString(first_name) && validateString(last_name)
				&& validateDate(day_of_birth, month_of_birth, year_of_birth)){

			if(existUsername){
				//the user already exist in the table USERS, return null
				userid = null;
			}
			else
			{
				Session session = null;
				try{
					session = HibernateUtil.currentSession();
					Transaction transaction = session.beginTransaction();

					//create a new user in the table
					Users userToInsert = new Users();
					userToInsert.setUsername(username);
					userToInsert.setPassword(password);
					userToInsert.setFirstName(first_name);
					userToInsert.setLastName(last_name);
					GregorianCalendar gregorianCalendar = new GregorianCalendar(
							Integer.parseInt(year_of_birth),
							Integer.parseInt(month_of_birth),
							Integer.parseInt(day_of_birth));
					userToInsert.setRegistrationDate(Calendar.getInstance().getTime());
					userToInsert.setDateOfBirth(gregorianCalendar.getTime());
					session.saveOrUpdate(userToInsert);

					//generator class="increment" is used for userid
					userid = userToInsert.getUserid();
					transaction.commit();

				}catch(HibernateException e){
					e.printStackTrace();
				}
				finally{
					HibernateUtil.closeSession();
				}
			}


		}
		return userid;

	}

	/***
	 * This function validates that given Integer is not null and is positive.
	 * @param integer - Integer, the number to validate.
	 * @return boolean value - true if the integer is not null and positive; false otherwise.
	 */
	private static boolean validateInteger(Integer integer) {
		return ((integer != null) && (integer > 0));
	}

	/***
	 * retrieves from the table MediaItems first top_n items order by mid.
	 * @param top_n - Integer, the top number
	 * @return List of objects MediaItems retrieves from the table MediaItems, 
	 * 		   first top_n items order by mid.
	 */
	public static List<Mediaitems> getTopNItems(Integer top_n) {
		Session session = null;
		List<Mediaitems> mediaitems = null;
		if(validateInteger(top_n)){
			try{
				session = HibernateUtil.currentSession();

				//rownum acts like SELECT TOP N in MS SQL-Server
				String query = "from Mediaitems where rownum<=" + top_n +" order by mid";
				mediaitems = session.createQuery(query).list();

			}catch(HibernateException e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

		return mediaitems;
	}

	private static List<?> validateEntity(String username, String password, String tableName) {
		Session session = null;
		List<?> list = null;
		if(validateString(username) && validateString(password)){
			try{
				session = HibernateUtil.currentSession();
				String query = "from " + tableName + " where username = '" + username +
						"'" + "and password = '"+password+"'";
				list = session.createQuery(query).list();
			}catch(HibernateException e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}
		return list;
	}

	/***
	 * The function compares received values with existing in the data base.
	 * @param username - String, the user name needed to be checked
	 * @param password - String, the user's password.
	 * @return USERID if the values are equal to the values in the table; otherwise NULL.
	 */
	public static Integer validateUser(String username, String password) {
		Integer userid = null;
		List<Users> usersList = (List<Users>) validateEntity(username, password, "Users");
		//check if something returned from the table
		if(usersList.size() > 0){
			Users user = usersList.get(0);

			//gets the userid and return it
			userid = user.getUserid();
		}

		return userid;

	}

	/***
	 * The function compares received values with existing in the data base.
	 * @param username - String, the administrator's username needed to be checked.
	 * @param password - String, the administrator's password.
	 * @return ADMINID if the values are equal to the values in the table; otherwise NULL.
	 */
	public static Integer validateAdministrator(String username, String password){

		Integer adminid = null;
		List<Administrators> usersList = (List<Administrators>) 
				validateEntity(username, password, "Administrators");

		//check if something returned from the table
		if(usersList.size() > 0){
			Administrators user = usersList.get(0);

			//gets the adminid and return it
			adminid = user.getAdminid();
		}			

		return adminid;

	}

	/***
	 * The function insert the row to the History table with current server time.
	 * server time determined by INSERTVEIWTIME trigger in the database.
	 * @param userid - String, the user id to be inserted.
	 * @param mid - String, the media item id to be inserted. 
	 */
	public static void insertToHistory(String userid, String mid) {
		Session session = null;
		if(validateString(userid) && validateString(mid)){
			try{
				session = HibernateUtil.currentSession();
				Transaction transaction = session.beginTransaction();

				//creates a new record
				HistoryId historyId = new HistoryId();
				historyId.setUserid(Integer.parseInt(userid));
				historyId.setMid(Integer.parseInt(mid));
				//VIEWTIME getting default value in the table: SYSDATE via INSERTVEIWTIME trigger

				History history = new History();
				history.setId(historyId);

				session.saveOrUpdate(history);
				transaction.commit();


			}catch(Exception e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

	}

	/***
	 * The function retrieves from the tables History and MediaItems users's items.
	 * @param userid - String, the user id to select by.
	 * @return List of pairs <title,viewtime> sorted by VIEWTIME in descending order.
	 */
	public static List<HibernatePair> getHistory(String userid) {
		List<HibernatePair> ans = null;
		Session session = null;
		if(validateString(userid)){
			try{
				session = HibernateUtil.currentSession();

				//HQL Join
				String query = "from History his join his.mediaitems where userid = '"+
						userid+"' order by viewtime desc";

				/*This query return List of Objects that each object is array with 2 spots,
				 * the first is an Histrory object and the second is Mediaitems object
				 */
				List<Object> historyList =  session.createQuery(query).list();

				if(historyList.size() > 0){

					//List to return
					ans = new Vector<HibernatePair>();
					for(Object o: historyList){

						/*casting from Object to Object [], taking the first place and casting it
						 * to History.  
						 */
						Date viewtime = ((History)((Object[])o)[0]).getId().getViewtime();

						/*casting from Object to Object [], taking the second place and casting it
						 * to Mediaitems.  
						 */
						String title = ((Mediaitems)(((Object[])o)[1])).getTitle();
						HibernatePair pair = new HibernatePair(title, viewtime);
						ans.add(pair);
					}
				}

			}catch(Exception e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

		return ans;
	}

	/***
	 * The function insert the row to the LoginLog table with current server time.
	 * server time determined by INSERTLOGINLOG trigger in the database.
	 * @param userid - String, the user's id.
	 */
	public static void insertToLog(String userid) {
		Session session = null;
		if(validateString(userid)){
			try{
				session = HibernateUtil.currentSession();
				Transaction transaction = session.beginTransaction();

				//create a new record
				LoginlogId loginlogid = new LoginlogId();
				loginlogid.setUserid(Integer.parseInt(userid));
				//LOGIN getting default value in the table: SYSDATE via INSERTLOGINLOG trigger

				Loginlog loginlog = new Loginlog();
				loginlog.setId(loginlogid);

				session.saveOrUpdate(loginlog);
				transaction.commit();

			}catch(Exception e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

	}

	/***
	 * The function retrieves from the table Users number of registered users in 
	 * the past n days.
	 * @param n - Integer, the number of pasted days.
	 * @return integer number represent the number of registered users in the past n days.
	 */
	public static Integer getNumberOfRegistredUsers(Integer n) {
		Integer ans = null;
		Session session = null;

		if(validateInteger(n)){

			try{
				session = HibernateUtil.currentSession();

				//the GregorianCalendar Object is initiated with the current time of client
				GregorianCalendar calendar = new GregorianCalendar();

				//subtracts the amounts of days 
				calendar.add(GregorianCalendar.DAY_OF_MONTH, -1*n);

				//HQL with parameters
				String query = "from Users where registrationDate between :start_date "
						+ "and :end_date";

				//setting the strat_date and end_date, and gets the list of users
				List<Users> usersList = session.createQuery(query)
						.setParameter("start_date", calendar.getTime())
						.setParameter("end_date", GregorianCalendar.getInstance().getTime())
						.list();

				//the number of users returned from the table are the number of registered users in the past n days 
				ans = usersList.size();
			}catch(Exception e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

		return ans;
	}

	/***
	 * The function retrieves from the table Users all users.
	 * @return List of objects Users.
	 */
	public static List<Users> getUsers() {
		Session session = null;
		List<Users> userList = null;

		try{
			session = HibernateUtil.currentSession();
			String query = "from Users";
			userList = session.createQuery(query).list();

		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			HibernateUtil.closeSession();
		}

		return userList;
	}

	/***
	 * The function retrieves from the table Users user's information. 
	 * @param userid - String, the user id.
	 * @return object Users.
	 */
	public static Users getUser(String userid) {
		Users user = null;
		Session session = null;

		if(validateString(userid)){
			try{
				session = HibernateUtil.currentSession();
				String query = "from Users where userid = '" + userid + "'";
				List<Users> userList = session.createQuery(query).list();
				user = userList.get(0);
			}catch(Exception e){
				e.printStackTrace();
			}
			finally{
				HibernateUtil.closeSession();
			}
		}

		return user;
	}

}
